package com.jdbc.base;

import org.junit.Test;

import java.sql.*;

public class JDBCOperation {
    @Test
    public void testQuerySingleRowAndCol() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("select count(*) as count from t_emp");
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            System.out.println(resultSet.getInt("count"));
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testInsert() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");

        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_emp(emp_name, emp_salary, emp_age) values (?,?,?)");

        preparedStatement.setString(1, "mike");
        preparedStatement.setDouble(2, 500);
        preparedStatement.setInt(3, 20);
        int i = preparedStatement.executeUpdate();

        if (i > 0) {
            System.out.println("插入成功！");
        } else {
            System.out.println("插入失败！");
        }

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("update t_emp set emp_salary = ? where emp_name = ?");
        preparedStatement.setDouble(1, 1800);
        preparedStatement.setString(2, "mike");

        int result = preparedStatement.executeUpdate();

        if (result > 0) {
            System.out.println("修改成功！");
        } else {
            System.out.println("修改失败！");
        }

        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDelete() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("delete from t_emp where emp_name = ?");
        preparedStatement.setString(1, "mike");

        int result = preparedStatement.executeUpdate();

        if (result > 0) {
            System.out.println("删除成功！");
        } else {
            System.out.println("删除失败！");
        }

        preparedStatement.close();
        connection.close();
    }
}
